USE master
go

IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases
	   WHERE name = 'diplom')
	DROP DATABASE diplom
go


IF (@@error != 0)
BEGIN
	PRINT "Error dropping database 'diplom'"
	SELECT syb_quit()
END
go


CREATE DATABASE diplom
	    ON diplom_dat = '500M' -- 128000 pages
	LOG ON diplom_log = '250M' -- 64000 pages
	WITH DURABILITY = FULL
	   , DML_LOGGING = FULL
go


use diplom
go

exec sp_changedbowner 'sa', true 
go

exec master.dbo.sp_dboption diplom, 'select into/bulkcopy', true
go

checkpoint
go

/*==============================================================*/
/* Login: Slava                                                 */
/*==============================================================*/
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE name = 'Slava')
	exec sp_droplogin 'Slava'


IF (@@error != 0)
BEGIN
	PRINT "Error CREATING Login 'Slava'"
	SELECT syb_quit()
END
go


exec  sp_addlogin  'Slava', '12345678', @defdb='diplom', @deflanguage='us_english', @auth_mech = 'ANY'

exec  sp_locklogin  'Slava', 'unlock'



go 


/*==============================================================*/
/* Role: Slava_role                                                 */
/*==============================================================*/



use master
go

IF EXISTS (SELECT 1 FROM master.dbo.syssrvroles where name = 'Slava_role')
	DROP ROLE 'Slava_role' with override


IF (@@error != 0)
BEGIN
	PRINT "Error CREATING Role 'Slava_role'"
	SELECT syb_quit()
END
go

create role Slava_role with passwd 'password'
go 

use diplom
go

/*==============================================================*/
/* Domain: title                                                */
/*==============================================================*/

execute sp_addtype title, 'varchar(50)', 'null'
go

/*==============================================================*/
/* Table: list_of_manufacturer                                  */
/*==============================================================*/

create table Manufacturer (
   ID_manufacturer      int                            not null,
   name_manufacturer    title                          not null,
   constraint PK_LIST_OF_MANUFACTURER primary key nonclustered (ID_manufacturer)
)
go

/*==============================================================*/
/* Table: category                                              */
/*==============================================================*/

create table Category (
   ID_category          int                            not null,
   name_category        title                          not null,
   ID_category_of_parent int                           not null,
   constraint PK_CATEGORY primary key nonclustered (ID_category)
)
go

/*==============================================================*/
/* Domain: price                                                */
/*==============================================================*/


create rule R_price as
      @column >= 0
go

if exists(select 1 from systypes where name='price')
   execute sp_droptype price
go

execute sp_addtype price, 'decimal(11,2)', 'null'
go

execute sp_bindrule 'R_price', price
go
/*==============================================================*/
/* Table: Goods                                                 */
/*==============================================================*/

create table Good (
   ID_manufacturer      int                            not null,
   model                varchar(20)                    not null,
   ID_category          int                            not null,
   price                price                          null,
   description          varchar(256)                   null,
   image                varchar(50)                    null,
   storage              char(1)                        not null
   constraint CKC_STORAGE_GOODS check (storage in ('Y','N')),
   constraint PK_GOODS primary key nonclustered (ID_manufacturer, model)
)
go

/*==============================================================*/
/* Index: Index_name_category                                   */
/*==============================================================*/
if exists (select 1
            from  sysindexes
           where  id    = object_id('category')
            and   name  = 'Index_name_category'
            and   indid > 0
            and   indid < 255)
   drop index category.Index_name_category
go

create clustered index Index_name_category on category (
name_category ASC
)
go

/*==============================================================*/
/* Index: Relationship_3_FK                                     */
/*==============================================================*/


create index Relationship_3_FK on Good (
ID_manufacturer ASC
)
go

/*==============================================================*/
/* Index: Relationship_2_FK                                     */
/*==============================================================*/


create index Relationship_2_FK on Good (
ID_category ASC
)
go

/*==============================================================*/
/* Index: Relationship_1_FK                                     */
/*==============================================================*/


create index Relationship_1_FK on Category (
ID_category_of_parent ASC
)
go

/*==============================================================*/
/* Index: Index_name_manufacturer                               */
/*==============================================================*/


create clustered index Index_name_manufacturer on Manufacturer (
name_manufacturer ASC
)
go

/*================================================================*/
/* References: ID_category, ID_manufacturer, ID_category_of_parent*/
/*================================================================*/


alter table Good
   add constraint FK_GOODS_RELATIONS_CATEGORY foreign key (ID_category)
      references Category (ID_category)
go



alter table Good
   add constraint FK_GOODS_RELATIONS_LIST_OF_ foreign key (ID_manufacturer)
      references Manufacturer (ID_manufacturer)
go


alter table Category
   add constraint FK_CATEGORY_RELATIONS_CATEGORY foreign key (ID_category_of_parent)
      references Category (ID_category)
go

/*==============================================================*/
/* View: View_1                                                 */
/*==============================================================*/


create view View_1 as
select
   
   Category.name_category,
   Manufacturer.name_manufacturer,
   Good.model,
   Good.description,
   Good.image,
    Good.price,
    Good.storage

from
   Category,
   Good,
   Manufacturer
go

/*==============================================================*/
/* Grant: Category                                              */
/*==============================================================*/
Grant Insert on dbo.Category to Slava 
go
Grant Delete on dbo.Category to Slava
go
Grant References on dbo.Category to Slava 
go
Grant Select on dbo.Category to Slava 
go
Grant Update on dbo.Category to Slava 
go
Grant Insert on dbo.Category to Slava_role 
go
Grant Delete on dbo.Category to Slava_role 
go
Grant References on dbo.Category to Slava_role 
go
Grant Select on dbo.Category to Slava_role 
go
Grant Update on dbo.Category to Slava_role 
go

/*==============================================================*/
/* Grant: Manufacturer                                  */
/*==============================================================*/
Grant Insert on dbo.Manufacturer to Slava 
go
Grant Delete on dbo.Manufacturer to Slava
go
Grant References on dbo.Manufacturer to Slava 
go
Grant Select on dbo.Manufacturer to Slava 
go
Grant Update on dbo.Manufacturer to Slava 
go
Grant Insert on dbo.Manufacturer to Slava_role 
go
Grant Delete on dbo.Manufacturer to Slava_role 
go
Grant References on dbo.Manufacturer to Slava_role 
go
Grant Select on dbo.Manufacturer to Slava_role 
go
Grant Update on dbo.Manufacturer to Slava_role 
go

/*==============================================================*/
/* Grant: Good                                                 */
/*==============================================================*/
Grant Insert on dbo.Good to Slava 
go
Grant Delete on dbo.Good to Slava
go
Grant References on dbo.Good to Slava 
go
Grant Select on dbo.Good to Slava 
go
Grant Update on dbo.Good to Slava 
go
Grant Insert on dbo.Good to Slava_role 
go
Grant Delete on dbo.Good to Slava_role 
go
Grant References on dbo.Good to Slava_role 
go
Grant Select on dbo.Good to Slava_role 
go
Grant Update on dbo.Good to Slava_role 
go
/*==============================================================*/
/* Inserts                                                      */
/*==============================================================*/



create proc insertManufacturer(@name varchar(20))
as
begin
declare @x int
set @x=(select max(ID_manufacturer) from dbo.Manufacturer)+1
select @x
insert into dbo.Manufacturer values(@x, @name)
end

go

create proc insertCategory(@nameCat varchar(20), @namePar varchar(20))
as
begin
declare @x int
set @x=(select max(ID_category) from dbo.Category)+1
select @x
declare @y int
set @y=(select ID_category from Category where name_category=@namePar)
select @y
insert into dbo.Category values(@x, @nameCat, @y)
end

go


GRANT EXECUTE ON insertCategory TO Slava